package com.DAO;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.beans.NoticeForm;

import com.connection.ConnectionFactory;

public class CategoryDAOold1 {
	

	private Connection con=null;
	
	public CategoryDAOold1()
	{
		
		con = ConnectionFactory.getConnection();
		
	}

	
	@SuppressWarnings("finally")
	public ArrayList<String> get() throws SQLException
	{	
			ArrayList<String> list = new ArrayList<String>();
			String query = "select * from Categories";
			Statement st = null;
			ResultSet rs = null;
			try 
			{

				st = con.createStatement();
				rs = st.executeQuery(query);
				while (rs.next()) {
					list.add(rs.getString("name"));
				}

			} catch (SQLException e)
			{
				e.printStackTrace();
			} finally 
			{
				/* close connection */
				try 
				{
					
					if (st != null) 
					{
						st.close();
					}
					if (rs != null) 
					{
						rs.close();
					}

				} catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		
				return list;			
			}
			
	}

	
	
	public int insert(String category) throws SQLException 
	{
		// TODO Auto-generated method stub
		String query = "INSERT into Categories(name) values(?)";
		PreparedStatement ps = null;
		ps=con.prepareStatement(query);
		ps.setString(1,category);
		int rowsAffected=0;
		try 
		{

			
			rowsAffected = ps.executeUpdate();
			
					
		} catch (SQLException e)
			{
				e.printStackTrace();
			} finally 
				{
					/* close connection */
					try 
					{
						if (ps != null) 
						{
							ps.close();
						}
			
					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				
				}
			return rowsAffected;
	}
	
	public int getCategoryId(String category) throws SQLException
	{
		String query = "Select id from categories Where name ='"+category+"'";
		Statement st = null;
		ResultSet rs = null;
		try 
		{
			st = con.createStatement();
			rs = st.executeQuery(query);
			if(rs.next()) {
				int id=rs.getInt("id");
				System.out.println("Category id is................."+id);
				return id;
			}

		}
		
		catch (SQLException e)
		{
			System.out.println("Error in category...........");
		}
		
		finally 
		{
			/* close connection */
			try 
			{
				
				if (st != null) 
				{
					st.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
				
		}
		return 0;
		
	}
	
	public void closeConnection()  throws SQLException
	{
		con.close();
		ConnectionFactory.closeConnection();
	}
		
	}

	
	


